package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.mysql.jdbc.Driver;

public class DBConnectorMySQL implements IDBConnector{
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement =  null;
	private ResultSet resultSet = null;
	private static DBConnectorMySQL instance = null;
	
	private static String connectionURL = "jdbc:mysql://db4free.net:3306/";
	private static String user = "aiphaw";
	private static String password = "aiphaw";
	private static String dbname = "hawaip";
	
	private DBConnectorMySQL(){
		
	}
	
	public void connect() throws ClassNotFoundException, SQLException{
		System.out.println("Connecting...");
	
			
		
		// This will load the MySQL driver, each DB has its own driver
		Class.forName("com.mysql.jdbc.Driver");
		// Setup the connection with the DB
		connect = DriverManager.getConnection(buildConnectionString());
		//connect = DriverManager.getConnection(connectionURL + "?" + "user=aiphaw&password=aiphaw");
		
//		resultSet = statement.executeQuery("select * from test");
//		writeResultSet(resultSet);
//		resultSet = statement.executeQuery("select * from test");
//		writeResultSet(resultSet);
	
	
	}
	
	private String buildConnectionString(){
		return connectionURL + dbname + "?" + "user=" + user + "&password=" + password;
	}
	
	public static DBConnectorMySQL getInstance() {
	        if (instance == null) {
	            instance = new DBConnectorMySQL();
	            try {
					instance.connect();
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	        }
	        return instance;
	    }

	
	public ResultSet query(String SQLStatement) throws SQLException{
        ResultSet result = null;;

            statement = connect.createStatement();
            
            result= statement.executeQuery(SQLStatement);
            return result;

	}
        
        public int update(String SQLStatement) throws SQLException{
             int result = 0;

            statement = connect.createStatement();
            
            result= statement.executeUpdate(SQLStatement);
            return result;
        }
	
	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			System.out.println("Result:");
			int id = resultSet.getInt("idtest");
			String test = resultSet.getString("muh");
			System.out.println(id + " " + test);
			
//			String user = resultSet.getString("myuser");
//			String website = resultSet.getString("webpage");
//			String summery = resultSet.getString("summery");
//			Date date = resultSet.getDate("datum");
//			String comment = resultSet.getString("comments");
//			System.out.println("User: " + user);
//			System.out.println("Website: " + website);
//			System.out.println("Summery: " + summery);
//			System.out.println("Date: " + date);
//			System.out.println("Comment: " + comment);
		}
	}

	
	private void disconnect() throws SQLException{
		close();
	}
	
	// You need to close the resultSet
	private void close() throws SQLException {
				if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		
	}

	
}
